#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2015年3月25日

@author: chenyongbing
'''
import sys,os,datetime,re
#current_dir = os.path.dirname(__file__)
current_dir = os.path.split(os.path.realpath(__file__))[0]
from localConfig import baseLocalConfig
from PyMySQL import ConnectMySQL
mofi_host = baseLocalConfig.mysqlConfig['zhimofi']['host']
mofi_user = baseLocalConfig.mysqlConfig['zhimofi']['user']
mofi_passwd = baseLocalConfig.mysqlConfig['zhimofi']['passwd']
mofi_db = baseLocalConfig.mysqlConfig['zhimofi']['db']

myMofi = ConnectMySQL(mofi_host,mofi_user,mofi_passwd,mofi_db)

from time_method import myTimeMethod
from retail_datas import RetailDatas
import logging
logger = logging.getLogger('base.mofi_datas')

class MofiDatas(RetailDatas):
    '''
    classdocs
    '''


    def __init__(self):
        '''
        Constructor
        '''
        self.myMofi = myMofi
    
    def check_ap_status_before(self,tab='devicelog',device=None,day=None):
        if day==None:
            day = datetime.date.today().strftime('%Y-%m-%d')
        query = 'select status,stamptime from %s where deviceid="%s" and date(stamptime)<"%s" order by stamptime desc  limit 1'%(tab,device,day)
        rets = myMofi.SelectAll(query)
        if rets==None or len(rets)==0:return 0
        return int(rets[0][0])
        
    def get_device_version(self,tab='devices',device=None):
        if device==None:return '1.0'
        
        query = 'select version from %s where deviceid="%s"'%(tab,device)
        ret = myMofi.SelectOne(query)
        if ret==None:return '1.0'
        version = ret[0]
        if version!=None:return '2.0'
        return '1.0'
    


   
    def get_ap_last_offline_time(self,tab='devicelog',device=None,version='1.0'):
        if device == '':
            logger.error('设备MAC(%s)错误.'%device)
            return 
        elif device!=None:
            if version == '1.0':
                query = 'select  deviceid,STATUS,stamptime from %s where deviceid="%s" order by stamptime desc limit 1'%(tab,device)
            elif version == '2.0':
                tab= 'platform_log'
                query = 'select object,if(content="设备离线",0,1),created_at from %s where object="%s" and content in ("设备离线","设备上线") order by created_at desc limit 1'%(tab,device)
            else:
                return None
        else:
            logger.error("get_ap_last_offline_time Parameters tab and device are required")
            return None
        ret = myMofi.SelectOne(query)
        if ret==None:
            logger.error('数据表%s 中未查到设备(%s)的相关记录'%(tab,device))
            return None
        else:
            deviceid,status,stamptime = ret
        now = datetime.datetime.today()
        if str(status)=='1':
            logger.info('设备(%s)在线：%s'%(device,stamptime.strftime('%Y-%m-%d %X')))
            return None
        else:
            logger.info('设备(%s)已于%s离线'%(device,stamptime.strftime('%Y-%m-%d %X')))
            return stamptime
    
    def stat_online_time(self,tab='devicelog',device=None,startTime=None,endTime=None):
        if device==None:
            logger.debug('device cannot null.')
            return '-'
        query = 'select status,stamptime from %s where deviceid="%s"'%(tab,device)
        if startTime!=None and endTime==None:
            query = query + ' and date(stamptime) ="%s"'%(startTime)
        elif startTime!=None and endTime!=None:
            query = query + ' and date(stamptime) between "%s" and "%s"'%(startTime,endTime)
        logger.debug(query)
        rets = myMofi.SelectAll(query)
        if rets==None:rets=[]
        
        if len(rets)==0 and startTime!=None:
            if self.check_ap_status_before(device=device, day=startTime):
                rets = [[1,datetime.datetime(int(startTime[:4]),int(startTime[5:7]),int(startTime[8:10]),23,59,59)]]
        
        total_time = 0
        stamptime_start=None
        stamptime_end=None
        today = datetime.date.today().strftime('%Y-%m-%d')
        for i in range(len(rets)):
            status,stamptime = rets[i]
            status = int(status)
            stamptime_s = int(stamptime.strftime('%s'))
            if i==0 and status==0:
                stamptime_end = stamptime_s
                if today==startTime or startTime==None:
                    stamptime_start = myTimeMethod.get_day_start_sec(day=stamptime.strftime('%Y-%m-%d'))
                else:
                    stamptime_start = myTimeMethod.get_day_start_sec(day=startTime)
            elif i==len(rets)-1 and status==1:
                stamptime_start = stamptime_s
                if today==endTime or endTime==None:
                    stamptime_end = int(datetime.datetime.today().strftime('%s'))
                else:
                    stamptime_end = myTimeMethod.get_day_end_sec(day=endTime)
            elif status==1:
                stamptime_start = stamptime_s
            elif status==0:
                stamptime_end = stamptime_s
            if stamptime_start!=None and stamptime_end!=None:
                logger.debug('stamptime_end - stamptime_start = %s - %s = %s'%(stamptime_end,stamptime_start,stamptime_end-stamptime_start))
                total_time = total_time + (stamptime_end - stamptime_start)
                stamptime_start=None
                stamptime_end=None
        logger.info('device total online time %ss'%(total_time))
        return total_time
               
    def get_ap_status(self,devices=[]):
        devStatus = {}
        if devices==[]:return devStatus
        if len(devices)==1:
            query = 'select deviceid , status from devicelog where (deviceid,stamptime) in (select deviceid,max(stamptime) from devicelog where deviceid in (%s) group by deviceid)'%('"'+devices[0]+'"') 
        else:
            query = 'select deviceid , status from devicelog where (deviceid,stamptime) in (select deviceid,max(stamptime) from devicelog where deviceid in (%s) group by deviceid)'%('"'+'","'.join(devices)+'"')
        rets = myMofi.SelectAll(query)
        if rets==None:rets=[]
        for device , status in rets:
            devStatus[device] = {'status':status}
        return devStatus
    
    
    def get_ap_status_from_devices(self,tab='devices',devices=[]):
        devStatus = {}
        if devices==[]:return devStatus
        query = 'select deviceid , status from %s'%tab
        if len(devices)==1:
            query += ' where deviceid in (%s)'%('"'+devices[0]+'"') 
        elif len(devices) == 0:
            pass
        else:
            query += ' where deviceid in (%s)'%('"'+'","'.join(devices)+'"')
        rets = myMofi.SelectAll(query)
        if rets==None:rets=[]
        for device , status in rets:
            devStatus[device] = {'status':status}
        return devStatus
    
    def get_ap_offline_time(self,devices=[]):
        devOfflines = {}
        if devices==[]:return {}
        if len(devices)==1:
            query = 'select deviceid,max(stamptime) from devicelog where status=0 and deviceid in (%s) group by deviceid'%('"'+devices[0]+'"')
        else:
            query = 'select deviceid,max(stamptime) from devicelog where status=0 and deviceid in (%s) group by deviceid'%('"'+'","'.join(devices)+'"')
        if len(devices) ==1 :
            query = 'select object,max(created_at) from platform_log where type="设备"  and content="设备离线"  and object in (%s) group by object'%('"'+devices[0]+'"')
        else:
            query = 'select object,max(created_at) from platform_log where type="设备" and content="设备离线"  and object in (%s) group by object'%('"'+'","'.join(devices)+'"')
        
        
        
        rets = myMofi.SelectAll(query)
        if rets==None:rets=[]
        
        for device ,last_offline in rets:
            devOfflines[device] = {'last_offline':last_offline.strftime('%Y-%m-%d %H:%M:%S')}
        return devOfflines
    
    def get_ap_online_time(self,devices=[]):
        devOnlines = {}
        if devices==[]:return {}
        if len(devices)==1:
            query = 'select deviceid,min(stamptime),max(stamptime) from devicelog where status=1 and deviceid in (%s) group by deviceid'%('"'+devices[0]+'"')
        else:
            query = 'select deviceid,min(stamptime),max(stamptime) from devicelog where status=1 and deviceid in (%s) group by deviceid'%('"'+'","'.join(devices)+'"')
        if len(devices) ==1 :
            query = 'select object,min(created_at),max(created_at) from platform_log where type="设备" and content="设备上线"  and object in (%s) group by object'%('"'+devices[0]+'"')
        else:
            query = 'select object,min(created_at),max(created_at) from platform_log where type="设备" and content="设备上线"  and object in (%s) group by object'%('"'+'","'.join(devices)+'"')
        
        rets = myMofi.SelectAll(query)
        if rets==None:rets=[]
        
        for device , first_online,last_online in rets:
            devOnlines[device] = {'first_online':first_online.strftime('%Y-%m-%d %H:%M:%S'),'last_online':last_online.strftime('%Y-%m-%d %H:%M:%S')}
            
        return devOnlines 
                


    def get_white_black_from_dev_config(self,devConfig={},para='white_list'):
        datas = []
        if not para in devConfig.keys():
            return datas
        
        for white_data in devConfig[para]:
            datas.append(white_data['content'])
        return datas

    def get_ap_config(self,tab='device_config',devices=[]):
        datas = {}
        if devices==[]:return datas
        if len(devices) == 1:
            query = 'select deviceid , content from %s where configKey=9 and deviceid="%s"'%(tab,devices[0])
        else:
            query = 'select deviceid , content from %s where configKey=9 and deviceid in (%s)'%(tab,'"'+'","'.join(devices)+'"')
        rets = myMofi.SelectAll(query)
        if rets==None:rets=[]
        for device , devConfig in rets:
            if  not re.search('^{.*}$',devConfig):  
                continue 
            devConfigDict = eval(devConfig)
            white_list = self.get_white_black_from_dev_config(devConfig=devConfigDict, para='white_list')
            black_list = self.get_white_black_from_dev_config(devConfig=devConfigDict, para='black_list')
            
            datas[device] = {'white_list':white_list,'black_list':black_list}
        return datas
        
            
myMofiDatas = MofiDatas()            
            
    
if __name__ == '__main__':
    myMofiDatas.stat_online_time( device='048d3861f688', startTime='2015-03-24', endTime='2015-03-24')